using System;
using System.Data;
using System.Collections;
using System.Text;

using EntLibEC.Cmn;
using System.Transactions;

using EntLibEC.Dac;
using EntLibEC.Dac.Online;

using EntLibEC.Info.Online;

namespace EntLibEC.Biz.Online
{
	/// <summary>
	/// Summary description for LinkSourceReportColumnManager.
	/// </summary>
	public class LinkSourceReportColumnManager
	{
		private LinkSourceReportColumnManager()
		{
		}

		private static LinkSourceReportColumnManager _instance;
		public static LinkSourceReportColumnManager GetInstance()
		{
			if( _instance == null )
			{
				_instance = new LinkSourceReportColumnManager();
			}
			return _instance;
		}

		public void Insert(LinkSourceReportColumnInfo oParam)
		{
			new LinkSourceReportColumnDac().Insert(oParam);
		}
		public void Update(Hashtable ht)
		{
			new LinkSourceReportColumnDac().Update(ht);
		}
		public void Delete(int sysNo)
		{
			new LinkSourceReportColumnDac().Delete(sysNo);
		}
		public DataSet GetDs()
		{
			string sql = @"select lsrc.*, username as CreateName from
							Ent_LinkSource_ReportColumn as lsrc
							inner join Ent_Sys_User
							on lsrc.createusersysno = Ent_Sys_User.sysno";
			return SqlHelper.ExecuteDataSet(sql);
		}
		public DataSet GetLinkSourceByColumn(Hashtable ht)
		{
			string sql = "select linksource from Ent_LinkSource_ReportColumn where status = " + (int)AppEnum.BiStatus.Valid;
			DataSet ds = SqlHelper.ExecuteDataSet(sql);
			if ( !Util.HasMoreRow(ds))
				return null;
			
			string sql_column = "";
			string sql_column_templet = " sum(case when urlsource=@url then visitcount else 0 end) as @url ";
			

			
			foreach(DataRow dr in ds.Tables[0].Rows)
			{
				if ( sql_column == "")
					sql_column = "select";
				else
					sql_column += ",";

				sql_column += sql_column_templet.Replace("@url", Util.ToSqlString(Util.TrimNull(dr["linksource"])));
			}

			if ( sql_column == "")
				return null;

			sql_column += " from Ent_LinkSource where 1=1 ";

			if ( ht.ContainsKey("DateFrom"))
				sql_column += " and countdate>=" + Util.ToSqlString(ht["DateFrom"].ToString());
			if ( ht.ContainsKey("DateTo"))
				sql_column += " and countdate<=" + Util.ToSqlString(ht["DateTo"].ToString());

			
			string sql1 = sql_column.Replace("select", "select countdate,");
			sql1 += " group by countdate ";

			string sql2 = sql_column.Replace("select", "select 'Total' as countdate,");

			return SqlHelper.ExecuteDataSet(sql1 + " union all " + sql2);
		}
	}
}
